if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExecuteCOINotification]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ExecuteCOINotification]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO 
/******************************************************************                  
* Name:   ExecuteCOINotification                  
* Purpose:  Notify/Remind users to fill out COIs                  
*                  
* PARAMETERS                  
* Name        Description                       
* -------------     -------------------------------------------                  
*  @type               Type of notification        
*  @institutionId      Institution under which the users have to be notified     
*  @lastActivityDate   Last activity date(for open enrollment)              
******************************************************************/                  
                  
CREATE Procedure [dbo].[ExecuteCOINotification]         
(                  
 @type Varchar(50),                
 @institutionId int = 0,                
 @lastActivityDate Datetime = NULL                
)                  
AS                  
BEGIN                  
                  
 SET NOCOUNT ON                  
                  
 DECLARE @TempPersonIds TABLE (                  
 [RowNum] INT IDENTITY (1,1),                  
 [Id] INT                  
 )                  
                   
 DECLARE @personCount INT                  
 DECLARE @recordIndex INT         
 Declare @isLastActivity bit         
                 
  if @lastActivityDate is NUll        
 BEGIN                
 Set @lastActivityDate =GetDate()        
 Set @isLastActivity = 0         
 END        
 Else        
 Set @isLastActivity =1               
                  
 if @institutionId != 0                
  BEGIN                
   DECLARE @TempInstitutionIds TABLE (                  
   [Id] INT,                
   [Name] Varchar(500)                
   )                  
   Insert Into @TempInstitutionIds(Id,[Name]) EXEC [dbo].GetInstitutionOrUnitOrDept @institutionId               
                  
   --Get the primary members associated with the organization(joining with AdmInternalPerson)                
   Insert @TempPersonIds(Id)                  
   SELECT DISTINCT adp.Id                 
    FROM AdmPerson adp                
    INNER JOIN AdmInternalPerson aip                
     On adp.Id = aip.AdmPersonId                
    INNER JOIN (Select Distinct Id from @TempInstitutionIds) tmpOId                
     On tmpOId.Id = aip.PrimaryOrganizationId                  
    INNER JOIN FwkDomainUser FDU                 
     ON adp.FwkDomainUserId = FDU.Id                  
    INNER JOIN FwkApplicationUser FAU                 
     ON FDU.Id = FAU.FwkDomainUserId                
    WHERE ModuleBitMask & 128 > 0                 
    UNION                
   --Get the Secondary members associated with the organization(joining with Admperson_FwkDomainOrganization_Map)                
    SELECT DISTINCT adp.Id                
    FROM AdmPerson adp                
    INNER JOIN Admperson_FwkDomainOrganization_Map afm                
     On adp.Id = afm.AdmPersonId                
    INNER JOIN (Select Distinct Id from @TempInstitutionIds) tmpOId                
     On tmpOId.Id = afm.FwkDomainOrganizationId                  
    INNER JOIN FwkDomainUser FDU                 
     ON adp.FwkDomainUserId = FDU.Id                  
    INNER JOIN FwkApplicationUser FAU                 
     ON FDU.Id = FAU.FwkDomainUserId                
    WHERE ModuleBitMask & 128 > 0                
                
  END                
 Else                
  BEGIN                
   INSERT @TempPersonIds (Id)                   
     (SELECT DISTINCT AdmPerson.Id                   
     FROM AdmPerson INNER JOIN FwkDomainUser FDU ON AdmPerson.FwkDomainUserId = FDU.Id                  
    INNER JOIN FwkApplicationUser FAU ON FDU.Id = FAU.FwkDomainUserId                  
     WHERE ModuleBitMask & 128 > 0                  
                     
     UNION                  
                     
     SELECT DISTINCT ChildId                   
     FROM AdmPerson INNER JOIN COI_Hierarchy_Map Map ON AdmPerson.Id = Map.ChildId       
     WHERE Map.Removed = 0)                  
 END                
 DECLARE @TempEmailPersonIds TABLE (                  
 [Id] INT                  
 )          
        
 --Select * from @TempPersonIds              
 IF @type = 'COINTFNTYPE_OPNENRLMNT'                  
 BEGIN                  
                   
   --Add COIs (If Start or InProgress COI exist, then update Notified Date. If submitted COI exist, then clone. Else add a new COI)                  
   SELECT @personCount = count(*) FROM @TempPersonIds                  
   DECLARE @admPersonId INT                  
   DECLARE @coiId INT                  
   SET @recordIndex = 1              
   WHILE (@recordIndex <= @personCount)                  
    BEGIN -- for each admperson id in temp table                  
                      
   SELECT @admPersonId = Id FROM @TempPersonIds WHERE RowNum = @recordIndex                  
   Set @coiId = 0                 
   --Get Start or InProgress coi id                  
   SET @coiId = (SELECT max(Id) FROM ConflictOfInterest                  
   WHERE status in ('COISTATUS_START', 'COISTATUS_INPROGRESS')                  
   AND admpersonid = @admPersonId)                  
                        
     IF (isnull(@coiId, 0) = 0)                   
    BEGIN                  
     --Get Submitted COI id                  
     SET @coiId = (SELECT max(Id) FROM ConflictOfInterest                  
     WHERE status in ('COISTATUS_SUBMITTED', 'COISTATUS_REVIEWED')                  
     AND admpersonid = @admPersonId)                  
             
      IF (isnull(@coiId, 0) = 0)                   
     BEGIN                  
      --print ' insert ' + cast(isnull(@admPersonId, 0) as varchar(10)) + ' ' + cast(isnull(@coiId, 0) as varchar(10))                  
      INSERT INTO ConflictOfInterest (AdmPersonId, LastNotified, Status)                  
      VALUES (@admPersonId, GetDate(), 'COISTATUS_START')         
      Insert into @TempEmailPersonIds values (@admPersonId)                 
     END                    
      ELSE                  
     BEGIN                  
      DECLARE @dateSubmitted DateTime                 
      Select @dateSubmitted = Isnull(DateSubmitted,'1/1/1900') from conflictOfInterest where Id = @coiId                
      if @lastActivityDate > @dateSubmitted                
       Begin                
       --print ' clone ' + cast(isnull(@admPersonId, 0) as varchar(10)) + ' ' + cast(isnull(@coiId, 0) as varchar(10))                  
       Exec CloneCOI @coiId         
       Insert into @TempEmailPersonIds values (@admPersonId)                
       End                 
     END                         
    END              
    ELSE                 
    BEGIN         
     if @isLastActivity = 0       
     --print ' update ' + cast(isnull(@admPersonId, 0) as varchar(10)) + ' ' + cast(isnull(@coiId, 0) as varchar(10))                  
     UPDATE ConflictOfInterest SET LastNotified = GetDate() WHERE Id = @coiId      
--     Else if  (@isLastActivity <> 0 and @lastActivityDate > (Select Isnull(DateCreated,'1/1/1900') from conflictOfInterest where Id = @coiId ))      
--      BEGIN      
--      UPDATE ConflictOfInterest SET LastNotified = GetDate() WHERE Id = @coiId      
--      Insert into @TempEmailPersonIds values (@admPersonId)        
--      END         
    END                      
   SET @coiId = 0                       
   SET @recordIndex = @recordIndex + 1                  
  END                     
 END                  
 ELSE                  
 BEGIN                 
       Insert @TempEmailPersonIds(Id)                
   (SELECT DISTINCT tmp.Id FROM @TempPersonIds tmp                 
   INNER JOIN                   
   ConflictOfInterest COI ON tmp.Id = COI.AdmPersonId                  
   AND COI.Status IN ('COISTATUS_START', 'COISTATUS_INPROGRESS')                  
  )                
    --Update Last Notified Date for all Start or InProgress COIs matching the admperson ids                  
    UPDATE ConflictOfInterest SET LastNotified = GetDate()                  
    WHERE status IN ('COISTATUS_START', 'COISTATUS_INPROGRESS')                  
    AND admpersonid IN ( Select Distinct(Id) from @TempEmailPersonIds)           
                   
 END                    
          

   declare @email varchar(100)
   execute @email = dbo.[GetServerEMail]

IF @type = 'COINTFNTYPE_OPNENRLMNT' and  @isLastActivity = 0          
 BEGIN          
-- --If No Activity date selected for open enrollment, Get All the Email Addresses        
  SELECT isNull(@email, E.EmailAddress)
  FROM AdmEmailAddress E INNER JOIN AdmEmailAddressType ET ON E.AdmEmailAddressTypeId = ET.Id                  
  WHERE ET.Name = 'Primary'                  
  AND E.EmailAddress IS NOT NULL                  
  AND E.EmailAddress <> ''                  
  AND E.AdmPersonId IN (SELECT Distinct Id FROM @TempPersonIds)                 
 End                 
Else         
 BEGIN         
        
-- --If Activity date is selected for open enrollment, Get the emails for those who are notified              
  SELECT isNull(@email, E.EmailAddress)
  FROM AdmEmailAddress E INNER JOIN AdmEmailAddressType ET ON E.AdmEmailAddressTypeId = ET.Id                  
  WHERE ET.Name = 'Primary'                  
  AND E.EmailAddress IS NOT NULL                  
  AND E.EmailAddress <> ''                  
  AND E.AdmPersonId IN (SELECT distinct Id FROM @TempEmailPersonIds)        
END                  
                  
                  
 Return                  
                  
 ERROR_HANDLER:                  
  RAISERROR('Unable to Execute COI Notification', 16, 1)                  
  RETURN                  
                    
END
go
